package com.wmobile.utils;

import java.lang.reflect.Field;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.database.Cursor;
import android.os.Handler;
import android.os.Message;

public class DbService {

	public DataHelper dbHelper;

	public DbService(DataHelper dbHelper) {
		this.dbHelper = dbHelper;
	}

	public SimpleDateFormat stdSdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	protected void sendMessage(Handler handler,
			ServiceResponse webserviceResponse) {
		Message msg = handler.obtainMessage();
		msg.obj = webserviceResponse;
		handler.sendMessage(msg);
	}

	/**
	 * 保存记录
	 * 
	 * @param obj
	 *            要保存的对象
	 * @return 返回处理结果
	 */
	public ServiceResponse save(DbObjects obj) {
		// insert
		if (obj.getId() == 0) {

			StringBuilder sb = new StringBuilder();
			StringBuilder sb1 = new StringBuilder();
			Class<? extends DbObjects> cls = obj.getClass();
			Field[] fields = cls.getDeclaredFields();

			try {
				for (Field fd : fields) {
					if (fd.getName().equals("id"))
						continue;
					fd.setAccessible(true);
					if (sb.length() == 0) {
						sb.append(fd.getName());
						if (fd.getType().getName().equals("int")
								|| fd.getType().getName().equals("long")
								|| fd.getType().getName().equals("double")
								|| fd.getType().getName().equals("float")) {
							sb1.append(fd.get(obj));
						} else if (fd.getType().getName()
								.equals("java.util.Date")) {
							Date tempDate = (Date) fd.get(obj);
							sb1.append("'" + stdSdf.format(tempDate) + "'");
						} else {
							if (fd.get(obj) == null) {
								sb1.append("''");
							} else {
								sb1.append("'" + fd.get(obj) + "'");
							}
						}
					} else {
						sb.append("," + fd.getName());
						if (fd.getType().getName().equals("int")
								|| fd.getType().getName().equals("long")
								|| fd.getType().getName().equals("double")
								|| fd.getType().getName().equals("float")) {
							sb1.append("," + fd.get(obj));
						} else if (fd.getType().getName()
								.equals("java.util.Date")) {
							Date tempDate = (Date) fd.get(obj);
							sb1.append(",'" + stdSdf.format(tempDate) + "'");
						} else {
							if (fd.get(obj) == null) {
								sb1.append(",''");
							} else {
								sb1.append(",'" + fd.get(obj) + "'");
							}
						}
					}
					fd.setAccessible(false);
				}
			} catch (IllegalArgumentException e) {
				return new ServiceResponse(false, e.getMessage());
			} catch (IllegalAccessException e) {
				return new ServiceResponse(false, e.getMessage());
			}
			String sql = "insert into " + cls.getSimpleName() + "("
					+ sb.toString() + ") values(" + sb1.toString() + ");";
			ServiceResponse response = execSql(sql);
			if (!response.getExeFlag()) {
				return response;
			}
			String idSql = "select LAST_INSERT_ROWID() as [newid];";
			Cursor c = this.dbHelper.db.rawQuery(idSql, null);
			if (c.moveToNext()) {
				obj.setId(c.getLong(0));
			}
			c.close();
			return new ServiceResponse(true, obj);
		}
		// update
		else {

			StringBuilder sb = new StringBuilder();
			Class<? extends DbObjects> cls = obj.getClass();
			Field[] fields = cls.getDeclaredFields();

			try {
				for (Field fd : fields) {
					if (fd.getName().equals("id"))
						continue;
					fd.setAccessible(true);
					if (sb.length() == 0) {
						if (fd.getType().getName().equals("int")
								|| fd.getType().getName().equals("long")
								|| fd.getType().getName().equals("double")
								|| fd.getType().getName().equals("float")) {
							sb.append(fd.getType().getName() + " = "
									+ fd.get(obj));
						} else if (fd.getType().getName()
								.equals("java.util.Date")) {
							Date tempDate = (Date) fd.get(obj);
							sb.append(fd.getName() + " = '"
									+ stdSdf.format(tempDate) + "'");
						} else {
							if (fd.get(obj) == null) {
								sb.append(fd.getName() + " = ''");
							} else {
								sb.append(fd.getName() + " = '" + fd.get(obj)
										+ "'");
							}
						}
					} else {
						if (fd.getType().getName().equals("int")
								|| fd.getType().getName().equals("long")
								|| fd.getType().getName().equals("double")
								|| fd.getType().getName().equals("float")) {
							sb.append("," + fd.getName() + " = " + fd.get(obj));
						} else if (fd.getType().getName()
								.equals("java.util.Date")) {
							Date tempDate = (Date) fd.get(obj);
							sb.append("," + fd.getName() + " = '"
									+ stdSdf.format(tempDate) + "'");
						} else {
							if (fd.get(obj) == null) {
								sb.append("," + fd.getName() + " = ''");
							} else {
								sb.append("," + fd.getName() + " = '"
										+ fd.get(obj) + "'");
							}
						}
					}
					fd.setAccessible(false);
				}
			} catch (IllegalArgumentException e) {
				return new ServiceResponse(false, e.getMessage());
			} catch (IllegalAccessException e) {
				return new ServiceResponse(false, e.getMessage());
			}
			String sql = "update " + cls.getSimpleName() + " set "
					+ sb.toString() + " where id = " + obj.getId();
			ServiceResponse response = execSql(sql);
			if (!response.getExeFlag()) {
				return response;
			}
			return new ServiceResponse(true, obj);
		}
	}

	/**
	 * 根据ID删除记录
	 * 
	 * @param obj
	 *            要删除的对象
	 * @return 返回处理结果
	 */
	public ServiceResponse deleteById(Class<? extends DbObjects> cls, long id) {
		String sql = "delete from " + cls.getSimpleName() + " where id = " + id;
		return execSql(sql);
	}

	/**
	 * 根据ID查询记录
	 * 
	 * @param obj
	 *            要存放数据的对象
	 * @param id
	 *            要取数的ID
	 * @return 返回对象
	 */
	public ServiceResponse queryById(Class<? extends DbObjects> cls, long id) {
		StringBuilder sb = new StringBuilder();
		Field[] fields = cls.getDeclaredFields();

		for (Field fd : fields) {
			fd.setAccessible(true);
			if (sb.length() == 0) {
				sb.append(fd.getName());
			} else {
				sb.append("," + fd.getName());
			}
			fd.setAccessible(false);
		}

		String sql = "select id, " + sb.toString() + " from "
				+ cls.getSimpleName() + " where id = " + id;
		Cursor c = this.dbHelper.db.rawQuery(sql, null);
		DbObjects obj = null;
		try {
			obj = cls.newInstance();
		} catch (IllegalAccessException e1) {
			return new ServiceResponse(false, e1.getMessage());
		} catch (InstantiationException e1) {
			return new ServiceResponse(false, e1.getMessage());
		}
		obj.setId(id);
		ServiceResponse response = null;
		if (c.moveToNext()) {
			for (Field fd : fields) {
				int columnIndex = c.getColumnIndex(fd.getName().toLowerCase());
				if (columnIndex < 0)
					continue;
				fd.setAccessible(true);
				try {
					if (fd.getType().getName().equals("int")) {
						fd.set(obj, c.getInt(columnIndex));
					} else if (fd.getType().getName().equals("long")) {
						fd.set(obj, c.getLong(columnIndex));
					} else if (fd.getType().getName().equals("double")) {
						fd.set(obj, c.getDouble(columnIndex));
					} else if (fd.getType().getName().equals("float")) {
						fd.set(obj, c.getFloat(columnIndex));
					} else if (fd.getType().getName().equals("java.util.Date")) {
						try {
							fd.set(obj, stdSdf.parse(c.getString(columnIndex)));
						} catch (ParseException e) {
							e.printStackTrace();
						}
					} else if (fd.getType().getName()
							.equals("java.lang.String")) {
						fd.set(obj, c.getString(columnIndex));
					}
				} catch (IllegalArgumentException e) {
					return new ServiceResponse(false, e.getMessage());
				} catch (IllegalAccessException e) {
					return new ServiceResponse(false, e.getMessage());
				}
				fd.setAccessible(false);
			}
			response = new ServiceResponse(true, obj);
		} else {
			response = new ServiceResponse(false, "data not found!");
		}
		c.close();
		return response;
	}

	/**
	 * 判断记录是否存在
	 */
	public boolean isRecordExist(String sql) {
		boolean result = false;
		Cursor c = this.dbHelper.db.rawQuery(sql, null);
		if (c.moveToNext()) {
			result = true;
		} else {
			result = false;
		}
		c.close();
		return result;
	}

	/**
	 * 重载查询
	 * 
	 * @param cls
	 *            返回对象类
	 * @param sql
	 *            查询语句
	 * @return 查询结果
	 */
	public ServiceResponse queryMass(Class<? extends DbObjects> cls, String sql) {
		return queryMass(cls, sql, 0, 0, false);
	}

	/**
	 * 查询
	 * 
	 * @param cls
	 *            返回对象类
	 * @param sql
	 *            查询语句
	 * @param pageNo
	 *            页码
	 * @param pageSize
	 *            每页记录数
	 * @return 查询结果
	 */
	public ServiceResponse queryMass(Class<? extends DbObjects> cls,
			String sql, int pageNo, int pageSize, boolean hasMultiPage) {
		long recordCount = 0;
		int pageCount = 0;
		Cursor dataCursor = null;
		if (pageSize > 0) {
			Cursor recordCursor = this.dbHelper.db.rawQuery(
					"select count(id) as recordCount from (" + sql + ")", null);
			if (recordCursor.moveToNext()) {
				recordCount = recordCursor.getLong(recordCursor
						.getColumnIndex("recordCount"));
				if (recordCount % pageSize == 0) {
					pageCount = Integer.parseInt(Long.toString(recordCount
							/ Long.parseLong(Integer.toString(pageSize))));
				} else {
					pageCount = Integer.parseInt(Long.toString(recordCount
							/ Long.parseLong(Integer.toString(pageSize)) + 1));
				}
				String sqlByPage = "select * from (" + sql + ") limit "
						+ String.valueOf(pageSize * (pageNo - 1)) + ", "
						+ pageSize;
				dataCursor = this.dbHelper.db.rawQuery(sqlByPage, null);
			}
			recordCursor.close();
		} else {
			dataCursor = this.dbHelper.db.rawQuery(sql, null);
		}
		DbObjectPages page = new DbObjectPages();
		List<DbObjects> dbObjects = new ArrayList<DbObjects>();
		DbObjects dbObject = null;
		Field[] fields = cls.getDeclaredFields();
		while (dataCursor.moveToNext()) {
			try {
				dbObject = cls.newInstance();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (InstantiationException e) {
				e.printStackTrace();
			}

			try {
				dbObject.setId(dataCursor.getLong(dataCursor
						.getColumnIndex("id")));
				for (Field fd : fields) {
					int columnIndex = dataCursor.getColumnIndex(fd.getName()
							.toLowerCase());
					if (columnIndex < 0)
						continue;
					fd.setAccessible(true);
					if (fd.getType().getName().equals("int")) {
						fd.set(dbObject, dataCursor.getInt(columnIndex));
					} else if (fd.getType().getName().equals("long")) {
						fd.set(dbObject, dataCursor.getLong(columnIndex));
					} else if (fd.getType().getName().equals("double")) {
						fd.set(dbObject, dataCursor.getDouble(columnIndex));
					} else if (fd.getType().getName().equals("float")) {
						fd.set(dbObject, dataCursor.getFloat(columnIndex));
					} else if (fd.getType().getName().equals("java.util.Date")) {
						try {
							fd.set(dbObject, stdSdf.parse(dataCursor
									.getString(columnIndex)));
						} catch (ParseException e) {
							e.printStackTrace();
						}
					} else if (fd.getType().getName()
							.equals("java.lang.String")) {
						fd.set(dbObject, dataCursor.getString(columnIndex));
					}
					fd.setAccessible(false);
				}
			} catch (IllegalArgumentException e) {
				return new ServiceResponse(false, e.getMessage());
			} catch (IllegalAccessException e) {
				return new ServiceResponse(false, e.getMessage());
			}
			dbObjects.add(dbObject);
		}
		dataCursor.close();
		page.setDbObjects(dbObjects);
		page.setPageNo(pageNo);
		page.setPageSize(pageSize);
		page.setPageCount(pageCount);
		page.setRecordCount(recordCount);
		if (hasMultiPage) {
			return new ServiceResponse(true, page);
		} else {
			return new ServiceResponse(true, dbObjects);
		}
	}

	/**
	 * 根据类获取Select语句
	 * 
	 * @param cls
	 *            对象类
	 * @return 返回查询语句
	 */
	protected String generateSelectSql(Class<? extends DbObjects> cls) {
		StringBuilder sb = new StringBuilder();
		Field[] fields = cls.getDeclaredFields();

		for (Field fd : fields) {
			fd.setAccessible(true);
			if (sb.length() == 0) {
				sb.append(fd.getName());
			} else {
				sb.append("," + fd.getName());
			}
			fd.setAccessible(false);
		}

		String sql = "select id, " + sb.toString() + " from "
				+ cls.getSimpleName();
		return sql.toLowerCase();
	}

	/**
	 * 查询重载
	 * 
	 * @param sql
	 *            查询语句
	 * @return 查询结果 DbObjectPages
	 */
	public ServiceResponse queryMass(String sql) {
		return queryMass(sql, 0, 0, false);
	}

	/**
	 * 查询重载
	 * 
	 * @param sql
	 *            查询语句
	 * @param pageNo
	 *            页码
	 * @param pageSize
	 *            每页记录数
	 * @return 查询结果 DbObjectPages
	 */
	public ServiceResponse queryMass(String sql, int pageNo, int pageSize,
			boolean hasMultiPage) {
		long recordCount = 0;
		int pageCount = 0;
		Cursor dataCursor = null;
		if (pageSize > 0) {
			Cursor recordCursor = this.dbHelper.db.rawQuery(
					"select count(id) as recordCount from (" + sql + ")", null);
			if (recordCursor.moveToNext()) {
				recordCount = recordCursor.getLong(recordCursor
						.getColumnIndex("recordCount"));
				if (recordCount % pageSize == 0) {
					pageCount = Integer.parseInt(Long.toString(recordCount
							/ Long.parseLong(Integer.toString(pageSize))));
				} else {
					pageCount = Integer.parseInt(Long.toString(recordCount
							/ Long.parseLong(Integer.toString(pageSize)) + 1));
				}
				String sqlByPage = "select * from (" + sql + ") limit "
						+ String.valueOf(pageSize * (pageNo - 1)) + ", "
						+ pageSize;
				dataCursor = this.dbHelper.db.rawQuery(sqlByPage, null);
			}
			recordCursor.close();
		} else {
			dataCursor = this.dbHelper.db.rawQuery(sql, null);
		}
		DbObjectPages page = new DbObjectPages();
		List<Map<String, Object>> dbObjects = new ArrayList<Map<String, Object>>();
		HashMap<String, Object> map = null;
		while (dataCursor.moveToNext()) {
			map = new HashMap<String, Object>();
			for (int i = 0; i < dataCursor.getColumnCount(); i++) {
				map.put(dataCursor.getColumnName(i), dataCursor.getString(i));
			}
			dbObjects.add(map);
		}
		dataCursor.close();
		page.setDbObjects(dbObjects);
		page.setPageNo(pageNo);
		page.setPageSize(pageSize);
		page.setPageCount(pageCount);
		page.setRecordCount(recordCount);
		if (hasMultiPage) {
			return new ServiceResponse(true, page);
		} else {
			return new ServiceResponse(true, dbObjects);
		}
	}

	/**
	 * @param sql
	 *            要执行的SQL
	 * @return 返回处理结果
	 */
	public ServiceResponse execSql(String sql) {
		try {
			this.dbHelper.db.execSQL(sql);
			return new ServiceResponse(true);
		} catch (Exception e) {
			return new ServiceResponse(false, e.getMessage());
		}
	}

	/**
	 * 开启事务
	 */
	public void beginTransaction() {
		this.dbHelper.db.beginTransaction();
	}

	/**
	 * 关闭事务
	 */
	public void commitTransaction() {
		this.dbHelper.db.setTransactionSuccessful();
		this.dbHelper.db.endTransaction();
	}

	/**
	 * 关闭事务
	 */
	public void rollbackTransaction() {
		this.dbHelper.db.endTransaction();
	}
}